package com.info.zhiduoduo.repository.mapper.company;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.account.ExportQuarterStudioDto;
import com.info.zhiduoduo.repository.dto.account.MonthZhiduoTradingVolumeDto;
import com.info.zhiduoduo.repository.dto.account.ReceivableDto;
import com.info.zhiduoduo.repository.dto.company.CompanyBriefDto;
import com.info.zhiduoduo.repository.dto.company.CompanyManagerDto;
import com.info.zhiduoduo.repository.dto.crowdsource.ExportProjectAcceptanceDto;
import com.info.zhiduoduo.repository.dto.invoice.CountByDate;
import com.info.zhiduoduo.repository.dto.project.*;
import com.info.zhiduoduo.repository.dto.statistics.StatisticLastThreeMonthsDto;
import com.info.zhiduoduo.repository.entity.company.CompanyProject;
import com.info.zhiduoduo.repository.entity.invoice.Invoice;
import com.info.zhiduoduo.repository.entity.maker.MakerStudio;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author TerryJ
 * @since 2018-12-08
 */
public interface CompanyProjectMapper extends BaseMapper<CompanyProject> {

    @Select(" select zcp.projectid,zcp.parent_projectid,zcp.projectname,zcp.type,zc.compid,zcp.compname,zcp.compnickname,zc.complogo," +
            " zcp.projectamount,zcp.projectdealamount,zcp.invoicetype,projectsubmit,projectstart,projectfinish,zcp.biddingstart,zcp.biddingend," +
            " zcp.pay_id,zcp.offline_pay_state,zcp.state,zcp.bid_type,zcp.bid_end,zcp.notify_upload_delivery," +
            " zcp.bid_project_finish,zcp.bid_verify_remark," +
            " (select count(distinct mzpb.userid) from zdd_maker_zdd_project_bid mzpb where mzpb.project_id = zcp.projectid) as bidCount," +
            " if(EXISTS(select * from zdd_maker_zdd_project_bid mzpb1 where mzpb1.project_id = zcp.projectid " +
            " and mzpb1.userid = #{userid}), 1, 0) as bid " +
            " from zdd_company_project zcp " +
            " join zdd_company zc ON zcp.compid = zc.compid " +
            " left join zdd_maker zu ON zcp.userid = zu.userid " +
            " left join zdd_maker_account ma on ma.userid = zu.userid " +
            " left join zdd_maker_studio zus on zus.userid = zu.userid " +
            " where (zcp.userid = #{userid} or EXISTS(select * from zdd_maker_zdd_project_bid mzpb1 where mzpb1.project_id = zcp.projectid " +
            "  and mzpb1.userid = #{userid} and state in (#{bidStatus}) ))" +
            " and zcp.type = 1 " +
            " and CONCAT(IFNULL(zcp.projectname,''), IFNULL(zcp.compnickname,''),IFNULL(zcp.realname,''),IFNULL(zcp.username,'')) like '%${name}%' " +
            " and if(#{state}=0, zcp.state>1, zcp.state=#{state}) " +
            " order by projectfinish desc,projectcheckend desc,projectcheckstart desc,zcp.createtime desc")
    List<UserProjectDto> queryProjectForUser(@Param("bidStatus") String bidStatus,@Param("userid") int userid, @Param("name") String name, @Param("state") int state);

    @Select(" select zcp.projectid,zcp.parent_projectid,zcp.projectname,zcp.type,spc.projectcategory as categoryname,zcp.categoryid," +
            " zc.compid,zcp.compnickname,zc.complogo," +
            " zcp.projectamount,zcp.projectdealamount,zcp.invoicetype,projectsubmit,projectstart,projectfinish,zcp.biddingstart,zcp.biddingend," +
            " zcp.pay_id,zcp.offline_pay_state,zcp.state,zcp.bid_type,zcp.bid_end,zcp.notify_upload_delivery, " +
            " zcp.bid_project_finish,zcp.bid_verify_remark,  " +
            " (select count(distinct mzpb.userid) from zdd_maker_zdd_project_bid mzpb where mzpb.project_id = zcp.projectid) as bidCount," +
            " if(EXISTS(select * from zdd_maker_zdd_project_bid mzpb1 where mzpb1.project_id = zcp.projectid " +
            " and mzpb1.userid = #{userid}), 1, 0) as bid " +
            " from zdd_company_project zcp " +
            " join zdd_company zc ON zcp.compid = zc.compid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = zcp.categoryid " +
            " where " +
            " zcp.type = 1 " +
            " and zcp.bid_type = 2 " +
            " and CONCAT(IFNULL(zcp.projectname,''),IFNULL(zcp.compnickname,''),IFNULL(spc.projectcategory,'')) like '%${name}%' " +
            " and zcp.state in (4,6,7) " +
            " and zcp.bid_end >= CURDATE() " +
            " order by projectfinish desc,projectcheckend desc,projectcheckstart desc,zcp.createtime desc")
    List<UserProjectDto> listProjectBidPoolForUser(@Param("name") String name, @Param("userid") int userid);

    @Select("select zcp.projectid,zcp.projectname,spc.projectcategory as categoryname,zcp.categoryid,zcp.parent_projectid,zcp.type,zcp.projecttype,zcp.invoicetype," +
            " zu.userid,zcp.username,zus.studionickname,zu.avatar,zcp.projectamount,zcp.projectdealamount," +
            " projectsubmit,projectstart,projectfinish,zcp.pay_id,zcp.offline_pay_state,zcp.biddingstart,zcp.biddingend," +
            " zcp.state,zcp.bid_type,zcp.bid_end,zcp.bid_project_finish,zcp.bid_verify_remark,zcp.delivery_path,zcp.notify_upload_delivery," +
            " zcp.attachment_url,zcp.remarks_field,zcp.error_code,zcp.error_msg,zcp.createtime, " +
            " (select count(distinct mzpb.userid) from zdd_maker_zdd_project_bid mzpb where mzpb.project_id = zcp.projectid) as bidCount " +
            " from zdd_company_project zcp " +
            " left join zdd_maker zu ON zcp.userid = zu.userid " +
            " left join zdd_maker_account ma on ma.userid = zu.userid " +
            " left join zdd_maker_studio zus on zus.userid = zu.userid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = zcp.categoryid " +
            " where zcp.compid = #{compid} " +
            " and zcp.type = 1 " +
            " and CONCAT(IFNULL(zcp.projectname,''),IFNULL(zcp.realname,''),IFNULL(zcp.username,'')) like '%${name}%' " +
            " and if(#{state}=0, zcp.state>0, zcp.state=#{state}) " +
            " ORDER BY zcp.state ASC,zcp.projectid DESC")
    List<CompanyProjectDto> queryProjectForCompany(@Param("compid") int compid, @Param("name") String name, @Param("state") int state);

    @Select("select cp.* " +
            " from zdd_company_project cp " +
            " left join zdd_maker_studio us on us.userid = cp.userid " +
            " left join zdd_company c on c.compid = cp.compid " +
            " where cp.projectid = #{projectid}")
    CompanyProject getCompanyProject(int projectid);

    @Select(" select projectid " +
            " from zdd_company_project " +
            " where state = 12 " +
            " and TIMESTAMPDIFF(DAY,projectcheckstart,NOW()) > #{days} ")
	List<Integer> getAutoCheckProjectids(@Param("days") int autoCheckProjectDays);

    @Select(" select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project " +
            " where IF(#{userid}=0, 1=1, userid = #{userid}) " +
            " and state in(${states}) " +
            " and projectid != #{projectid} " +
            " and IF(#{year}=0, YEAR(createtime) = YEAR(NOW()), YEAR(createtime) = #{year}) " +
            " and IF(#{quarter}=0,QUARTER(createtime) = QUARTER(NOW()),QUARTER(createtime) = #{quarter} )")
    BigDecimal getQuarterTotalAmountByStates(@Param("userid") Integer userid, @Param("projectid") int projectid, @Param("states") String states, @Param("year") int year,
                                             @Param("quarter") int quarter);

    @Select(" select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project " +
            " where userid = #{userid} " +
            " and state in(13,15,20) " +
            " and YEAR(createtime) = YEAR(NOW()) " +
            " and QUARTER(createtime) = QUARTER(NOW()) " +
            " and projectcheckend < #{date}")
    BigDecimal getQuarterTotalAmountBefore(@Param("userid") Integer userid, Date date);

    @Select(" select IFNULL(SUM(projectamount),0) " +
            " from zdd_company_project " +
            " where if(#{userid}=0, 1=1, userid = #{userid}) " +
            " and if(#{compid}=0, 1=1, compid = #{compid}) " +
            " and state in(10,12,13,15,20) " +
            " and projectid != #{projectid} " +
            " and YEAR(projectstart) = YEAR(NOW()) " +
            " and IF(#{quarter}=0,QUARTER(createtime) = QUARTER(NOW()),QUARTER(createtime) = #{quarter} )")
    BigDecimal getQuarterTotalAmountByCompid(@Param("userid") Integer userid, @Param("compid") int compid, @Param("projectid") int projectid, @Param("quarter") int quarter);

    @Select(" select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project " +
            " where IF(#{userid}=0, 1=1, userid = #{userid}) " +
            " and state in(${states}) " +
            " and projectid != #{projectid} " +
            " and YEAR(createtime) = #{year}")
    BigDecimal getYearlyTotalAmountByStates(@Param("userid") Integer userid, @Param("projectid") Integer projectid, @Param("states") String states, @Param("year") int year);

    @Select(" select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project " +
            " where userid = #{userid} " +
            " and state in(${states}) " +
            " and projectid != #{projectid} " +
            " and createtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH)")
    BigDecimal getTwelveMonthTotalAmountByStates(@Param("userid") Integer userid, int projectid, @Param("states") String states);

    @Select(" select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project " +
            " where userid = #{userid} " +
            " and state in(13,15,20) " +
            " and YEAR(createtime) = YEAR(NOW()) " +
            " and projectcheckend < #{date}")
    BigDecimal getYearlyTotalAmountBefore(@Param("userid") Integer userid, Date date);

    @Select(" select IFNULL(SUM(projectamount),0) " +
            " from zdd_company_project " +
            " where if(#{userid}=0, 1=1, userid = #{userid}) " +
            " and if(#{compid}=0, 1=1, compid = #{compid}) " +
            " and state in(10,12,13,15,20) " +
            " and projectid != #{projectid} " +
            " and YEAR(createtime) = YEAR(NOW())")
    BigDecimal getYearlyTotalAmountByCompid(@Param("userid") Integer userid, @Param("compid") int compid, @Param("projectid") Integer projectid);

    @Select(" select IFNULL(SUM(projecttaxfee),0) " +
            " from zdd_company_project " +
            " where userid = #{userid} " +
            " and state in(${states}) " +
            " and projectid != #{projectid} " +
            " and YEAR(projectcheckend) = YEAR(NOW())")
    BigDecimal getMakerYearlyTaxFeeByStates(@Param("userid") int userid, @Param("projectid") int projectid, @Param("states") String states);

    @Select(" select IFNULL(SUM(projecttaxfee),0) " +
            " from zdd_company_project " +
            " where userid = #{userid} " +
            " and state in(13,15,20) " +
            " and YEAR(projectcheckend) = YEAR(NOW()) " +
            " and projectcheckend < #{date}")
    BigDecimal getUserYearlyTaxFeeBefore(@Param("userid") int userid, Date date);

    @Select(" select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project " +
            " where userid = #{userid} " +
            " AND state in(${states}) " +
            " AND invoicetype = 1 " +
            " AND projectid != #{projectid} " +
            " and IF(#{year}=0, YEAR(createtime) = YEAR(NOW()), YEAR(createtime) = #{year}) " +
            " and IF(#{quarter}=0,QUARTER(createtime) = QUARTER(NOW()),QUARTER(createtime) = #{quarter} )")
    BigDecimal getQuarterGenProjectTotalAmountByStates(@Param("userid") int userid, @Param("projectid") int projectid, @Param("states") String states, @Param("year") int year,
                                                       @Param("quarter") int quarter);

    @Select(" select IFNULL(SUM(projectdealamount),0) from zdd_company_project " +
            " where userid = #{userid} " +
            " AND state in(13,15,20) " +
            " AND invoicetype = 1 " +
            " and YEAR(projectcheckend) = YEAR(NOW()) " +
            " AND QUARTER(projectcheckend) = QUARTER(NOW()) " +
            " and projectcheckend < #{date}")
    BigDecimal getQuarterGenProjectTotalAmountBefore(@Param("userid") int userid, Date date);

    @Select(" select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project " +
            " where userid = #{userid} " +
            " and state in(10,12,13,15,20) " +
            " and compid = #{compid} " +
            " and Date(projectstart) = CURDATE()")
    BigDecimal getTodayAmount(@Param("compid") int compid, @Param("userid") Integer userid);

    // 获取企业的项目数据 selectType=1：15分钟内交易的数据 selectType=1：根据项目ID查询
    @Select(" select projectid,userid,compid,realname,username,projectamount as projectAmount,2 as userType " +
            " from zdd_company_project " + " where compid = #{compid}" +
            " AND if(#{selectType} = 1,state in(${states}) and CURRENT_DATE() = DATE(projectsubmit) ,projectid in (${projectIds}) )")
    List<SameProjectDto> getTimeSlotProject(Integer selectType,Integer compid, String states, String projectIds);

    @Select(" select cp.projectid,cp.projectname,cp.projectdealamount as invoiceAmount," +
            " cp.invoicetype,cp.invoicecontent as content,cp.offline_pay_state,cp.projectfinish as projectfinishtime," +
            " us.userid,cp.username,us.creditcode as userTaxno,us.addr as userAddress,m.phone as userTelephone," +
            " ub.bankname as userAcctbank,ub.acctno as userAcctno," +
            " c.compid,cp.compname,c.creditcode as compTaxno,c.acctbank as compAcctbank," +
            " c.acctno as compAcctno,c.telephone as compTelephone,IFNULL(c.invoiceaddress,c.contactaddress) as compAddress," +
            " c.compemail as email,c.contactname,c.contactphone,cp.pay_id,cp.party_id " +
            " from zdd_company_project cp " +
            " left join zdd_maker_studio us on us.userid = cp.userid " +
            " left join zdd_maker m on m.userid = cp.userid " +
            " left join zdd_maker_bankcard ub on ub.userid = us.userid and ub.state = 1 and ub.accttype = 1 " +
            " join zdd_company c on c.compid = cp.compid " +
            " where cp.projectid = #{projectid} ")
    Invoice getPendingInvoiceDetail(@Param("projectid") Integer projectid);

    @Select("<script>" +
            " select projectid,projectname,cp.parent_projectid,cp.type,cp.compid,cp.compname,cp.compnickname,us.userid,cp.username," +
            " cp.realname,cp.supplement_time,cp.whether_supplement,sm.managername as supplementPersonnel,cp.useless_voucher,cp.useless_reason," +
            " categoryid,spc.projectcategory as categoryname,projectamount,projectdealamount,projectservicecharge,project_base_service_rate,project_partner_service_rate," +
            " projecttaxfee,project_tax_rate,projectcycle,projectsubmit,projectstart,projectfinish,projectcheckstart,projectcheckend, " +
            " cp.invoicetype,cp.pay_id,cp.offline_pay_state,cp.partner_withdraw_id,cp.remarks_field," +
            " ca.business_manager_id,bm.business_manager as businessManagerName, " +
            " cp.auto_check,cp.state,cp.arbitrate,cp.createtime,cp.invoicecontent as invoiceContent,cp.attachment_url,cp.delivery_path," +
            " cp.bid_type,cp.bid_end,cp.bid_project_finish,cp.bid_verify_remark,cp.notify_upload_delivery,cp.error_code,cp.error_msg, " +
            " if(exists(SELECT * from zdd_partner_company pc where pc.compid = cp.compid limit 1 ),'是','否') as hasPartner," +
            " bmm.business_manager as merchandiserName, " +
            " (select count(distinct mzpb.userid) from zdd_maker_zdd_project_bid mzpb where mzpb.project_id = cp.projectid) as bidCount," +
            " ccp.company_name as partyName,pay.pay_name,cp.amount_of_refund,cp.refund_time,cp.refund_voucher,smm.managername as refundRecordPersonName,cp.reason_for_refund  " +
            " from zdd_company_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
		    " join zdd_company_account ca on ca.compid = cp.compid " +
            " left join zdd_maker_studio us on us.userid = cp.userid " +
            " left join zdd_maker_account ma on ma.userid = us.userid " +
            " left join zdd_crowdsource_company_party ccp on cp.party_id =  ccp.id " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
		    " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " left join zdd_business_manager bmm on bmm.business_manager_id = ca.merchandiser_id  " +
            " left join zdd_system_manager sm on sm.managerid = cp.supplement_personnel" +
            " left join zdd_payment pay on cp.pay_id = pay.id " +
            " left join zdd_system_manager smm on smm.managerid = cp.refund_record_person_id " +
            " where if(#{state}=0, cp.state &gt; 1, cp.state = #{state}) " +
            " and if(#{categoryid} = 0 , 1=1, cp.categoryid = #{categoryid}) " +
            " and if(#{whetherSupplement} = 1, cp.whether_supplement = 1, 1=1) " +
            " and if(#{whetherSupplement} = 2, cp.whether_supplement = 0, 1=1)" +
            " AND IF(ISNULL(#{dockingCity}) OR #{dockingCity} = '',1=1,ma.opening_location in(#{dockingCity})) " +
            " AND IF(#{whetherThereIsARefund} = 0,1=1,cp.whether_there_is_a_refund = #{whetherThereIsARefund}) " +
            " <if test='startDate != null'>and cp.createtime &gt; #{startDate} </if>" +
            " <if test='endDate != null'>and cp.createtime &lt; #{endDate} </if>" +
            " <if test='projectStartDate != null'>and cp.projectfinish &gt; #{projectStartDate} </if>" +
            " <if test='projectEndDate != null'>and cp.projectfinish &lt; #{projectEndDate} </if>" +
            " ${strUploadFile} " +
            " and concat(cp.projectname,cp.compname,cp.username, cp.realname) like '%${name}%' " +
            " order by projectfinish desc,projectcheckend desc,projectcheckstart desc,cp.createtime desc " +
            "</script>")
    List<ProjectBriefDto> getProjectBriefList(@Param("name") String name, @Param("categoryid") int categoryid,
                                              @Param("state") int state, @Param("startDate") Date startDate,
                                              @Param("endDate") Date endDate,@Param("strUploadFile") String strUploadFile,
                                              @Param("whetherSupplement") int whetherSupplement,
                                              @Param("projectStartDate") Date projectStartDate,
                                              @Param("projectEndDate") Date projectEndDate,
                                              @Param("dockingCity") String dockingCity,
                                              @Param("whetherThereIsARefund") int whetherThereIsARefund);

    @Select(" select projectid,projectname,cp.parent_projectid,cp.type,c.compid,cp.compname,cp.compnickname,us.userid,cp.username," +
            " cp.realname,cp.supplement_time,cp.whether_supplement,sm.managername as supplementPersonnel,cp.delivery_path," +
            " categoryid,spc.projectcategory as categoryname,projectamount,projectdealamount,projectservicecharge,project_base_service_rate,project_partner_service_rate," +
            " projecttaxfee,project_tax_rate,projectcycle,projectsubmit,projectstart,projectfinish,projectcheckstart,projectcheckend, " +
            " cp.invoicetype,cp.pay_id,cp.offline_pay_state,cp.partner_withdraw_id," +
            " ca.business_manager_id,bm.business_manager as businessManagerName, " +
            " cp.auto_check,cp.state,cp.arbitrate,cp.createtime,cp.invoicecontent as invoiceContent,cp.attachment_url,cp.error_code,cp.error_msg," +
            " if(exists(SELECT * from zdd_partner_company pc where pc.compid = cp.compid limit 1 ),'是','否') as hasPartner,cp.long_term_project_remark " +
            " from zdd_company_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
		    " join zdd_company_account ca on ca.compid = cp.compid " +
            " left join zdd_maker_studio us on us.userid = cp.userid " +
            " left join zdd_maker_account ma on ma.userid = us.userid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
		    " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " left join zdd_system_manager sm on sm.managerid = cp.supplement_personnel " +
            " where cp.type = 4" +
            " and if(#{parentProjectid} = 0 ,1=1, cp.parent_projectid = #{parentProjectid}) " +
            " and if(#{whetherUploadFile}=0, 1=1, " +
            "       if(#{whetherUploadFile} = 1,((cp.attachment_url != '' and cp.attachment_url is not null) or cp.delivery_path is not null)," +
            "         ((cp.attachment_url is null or cp.attachment_url = '') and cp.delivery_path is null)" +
            "        )" +
            "      ) " +
            " and if(#{state}=0, cp.state > 1, cp.state = #{state}) " +
            " order by projectfinish desc,projectcheckend desc,projectcheckstart desc,cp.createtime desc ")
    List<ProjectBriefDto> listLongTermSubProjects(@Param("parentProjectid") int parentProjectid, @Param("state") int state, @Param("whetherUploadFile") int whetherUploadFile);

    @Select(" select projectid,projectname,cp.parent_projectid,cp.type,cp.compid,cp.compname,cp.userid,cp.username,cp.realname," +
            " categoryid,spc.projectcategory as categoryname,projectamount,projectdealamount,projectservicecharge,projecttaxfee,project_pay_amount," +
            " projectcycle,projectstart,projectfinish,cp.invoicetype,cp.pay_id,cp.offline_pay_state,cp.receiptimg,cp.remark," +
            " ca.business_manager_id,bm.business_manager as businessManagerName, " +
            " cp.auto_check,cp.state,cp.arbitrate,cp.createtime,cp.offline_pay_adminid as offlinePayAdminid," +
            " cp.party_id,(SELECT company_name from zdd_crowdsource_company_party where id = cp.party_id)as partyName, " +
            " if(exists(SELECT * from zdd_partner_company pc where pc.compid = cp.compid limit 1 ),'是','否') as hasPartner " +
            " from zdd_company_project cp " +
		    " left join zdd_company_account ca on ca.compid = cp.compid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
		    " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " where cp.state = #{state} " +
            " and cp.pay_id = 4 " +
            " and cp.createtime between #{startDate} and #{endDate} " +
            " and concat(cp.projectname,cp.compname,cp.username, cp.realname) like '%${name}%' " +
            " order by projectfinish desc,projectcheckend desc,projectcheckstart desc,cp.createtime desc ")
    List<ProjectJiugeOfflinePayDto> getJiugeOfflinePayProjectList(@Param("name") String name, @Param("state") int state,
                                                                  @Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select(" select projectid,projectname,cp.parent_projectid,cp.type,cp.compid,cp.compname,cp.compnickname," +
            " cp.userid,cp.username,cp.realname,cp.useless_voucher,cp.useless_reason,cp.pay_id,pay.pay_name," +
            " categoryid,spc.projectcategory as categoryname,projectamount,projectdealamount,projectservicecharge,projecttaxfee," +
            " projectcycle,projectsubmit,projectstart,projectfinish,cp.invoicetype,cp.offline_pay_state,cp.partner_withdraw_id," +
            " cp.auto_check,cp.state,cp.arbitrate,cp.createtime,cp.attachment_url,cp.offline_pay_adminid,cp.remarks_field,cp.delivery_path, " +
            " cp.bid_type,cp.bid_end,cp.bid_project_finish,cp.bid_verify_remark,cp.notify_upload_delivery,cp.error_code,cp.error_msg," +
            " (select count(distinct mzpb.userid) from zdd_maker_zdd_project_bid mzpb where mzpb.project_id = cp.projectid) as bidCount  " +
            " from zdd_company_project cp " +
            " left join zdd_payment pay on cp.pay_id = pay.id " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
            " where cp.compid = #{compid} " +
            " and cp.type = 1 " +
            " and if(#{state}=0, cp.state NOT IN(40,44) , cp.state = #{state}) " +
            " and if(#{whetherUploadFile}=0, 1=1, " +
            "       if(#{whetherUploadFile} = 1,((cp.attachment_url != '' and cp.attachment_url is not null) or cp.delivery_path is not null)," +
            "         ((cp.attachment_url is null or cp.attachment_url = '') and cp.delivery_path is null)" +
            "        )" +
            "      ) " +
            " and if(#{categoryid} = 0 , 1=1, cp.categoryid = #{categoryid}) " +
            " and DATE(cp.createtime) between DATE(#{startDate}) and DATE(#{endDate}) " +
            " and concat(IFNULL(cp.projectname,''),IFNULL(cp.compname,''),IFNULL(cp.username,''),IFNULL(cp.realname,'')) like '%${name}%'" +
            " order by cp.state,cp.createtime desc ")
    List<ProjectBriefDto> getProjectBriefListByCompid(@Param("compid") int compid, @Param("name") String name, @Param("categoryid") int categoryid, @Param("state") int state,
                                                      @Param("whetherUploadFile")int whetherUploadFile, @Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select("select cp.*,c.compemail,c.creditcode,us.specialinvoice,us.invoicescope," +
		    " ca.business_manager_id,bm.business_manager as businessManagerName,pay.pay_name " +
            " from zdd_company_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " join zdd_maker_studio us on us.userid = cp.userid " +
		    " join zdd_company_account ca on ca.compid = cp.compid " +
		    " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " left join zdd_payment pay on cp.pay_id = pay.id " +
            " where cp.projectid = #{projectid}")
    ProjectDto getProjectDetail(@Param("projectid") Integer projectid);

    @Select(" select SUM(cp.projectamount) " +
            " from zdd_company_project cp " +
            " where IF(#{compid}=0,1=1,cp.compid=#{compid}) " +
            " and cp.state in(10,12,13,15) ")
    BigDecimal getUnsettledProjectAmount(@Param("compid") int compid);

    @Insert(" <script>" +
            " insert into zdd_company_project " +
            " (compid,projectname,userid,categoryid,projecttype,projectcycle,projectintro,invoicetype,invoicecontent," +
            " projectamount,state,auto_check) " +
            " values " +
            " <foreach collection=\"list\" item=\"item\" separator=\",\">" +
            " (#{item.compid},#{item.projectname},#{item.userid},#{item.categoryid}," +
            " #{item.projecttype},#{item.projectcycle},#{item.projectintro},#{item.invoicetype},#{item.invoicecontent}," +
            " #{item.projectamount},#{item.state},#{item.autoCheck})" +
            " </foreach>" +
            " </script>")
    int insertList(@Param("list") List<CompanyProject> list);

    @Select(" select IFNULL(SUM(projectdealamount),0) as amount " +
            " from zdd_company_project cp " +
            " where compid = #{compid} " +
            " and cp.state in(13,15,20) " +
            " and DATE_FORMAT(projectcheckstart,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m')")
    BigDecimal getMonthAmountByCompid(Integer compid);

    @Select(" select IFNULL(SUM(projectamount),0) " +
            " from zdd_company_project cp " +
            " where IF(#{userid}=0, 1=1, userid = #{userid})" +
            " and projectid != #{projectid} " +
            " and if(#{invoiceType}=0, 1=1, cp.invoicetype = #{invoiceType})" +
            " and cp.state in(${states}) " +
            " and IF(#{year}=0, YEAR(projectstart) = YEAR(NOW()), YEAR(projectstart) = #{year}) " +
            " and IF(#{month}=0,MONTH(projectstart) = MONTH(NOW()),MONTH(projectstart) = #{month} )")
	BigDecimal getMonthTotalAmountByStates(@Param("userid") int userid, int projectid, int invoiceType, String states, int year, int month);

    @Select(" select projectid,projectname,cp.parent_projectid,cp.type,c.compid,cp.compname,cp.compnickname," +
            " us.userid,cp.username,cp.realname," +
            " categoryid,spc.projectcategory as categoryname,projectamount,projectdealamount,projectservicecharge,projecttaxfee," +
            " projectcycle,projectsubmit,projectstart,projectfinish,cp.invoicetype,cp.pay_id,cp.offline_pay_state,cp.partner_withdraw_id," +
            " cp.bid_type,cp.bid_end,cp.bid_project_finish,cp.bid_verify_remark,cp.delivery_path,cp.notify_upload_delivery," +
            " cp.auto_check,cp.state,cp.arbitrate,cp.createtime " +
            " from zdd_company_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " join zdd_maker_studio us on us.userid = cp.userid " +
            " join zdd_maker_account ma on ma.userid = cp.userid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
            " where cp.compid = #{compid} " +
            " and cp.userid = #{userid} " +
            " and cp.state = 20 " +
            " order by cp.projectid desc ")
	List<ProjectBriefDto> getprojectListByCompidAndUserid(int compid, int userid);

    @Select(" select cp.*,c.compemail,c.creditcode,us.specialinvoice,us.invoicescope," +
            " ca.business_manager_id,bm.business_manager as businessManagerName " +
            " from zdd_company_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " join zdd_maker_studio us on us.userid = cp.userid " +
            " join zdd_company_account ca on ca.compid = cp.compid " +
            " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " where cp.userid = #{userid} " +
            " and IF(#{compid}=0, 1=1, cp.compid = #{compid}) " +
            " and IF(#{state}=0, 1=1, cp.state = #{state}) " +
            " order by cp.projectid desc " +
            " limit 1 ")
	ProjectDto getuserLastProject(int compid, Integer userid, int state);

    @Select(" select * from zdd_company_project " +
            " where userid = #{userid} " +
            " and state in (${states}) " +
            " order by projectid desc " +
            " limit 1 ")
    CompanyProject getUserLastZddProject(Integer userid, String states);

    @Select(" select cp.userid, cp.realname, cp.username, IFNULL(SUM(cp.projectdealamount),0) as quarterAmount " +
            " from zdd_company_project cp " +
            " join zdd_maker_account ma on ma.userid = cp.userid " +
            " join zdd_maker_studio ms on ms.userid = cp.userid " +
            " where cp.state in (13,15,20) " +
            " and IF(#{year}=0, YEAR(projectcheckend) = YEAR(NOW()),  YEAR(projectcheckend) = #{year}) " +
            " and IF(#{quarter}=0,QUARTER(projectcheckend) = QUARTER(NOW()),QUARTER(projectcheckend) = #{quarter} ) " +
            " group by cp.userid " +
            " having IFNULL(SUM(cp.projectdealamount),0) >= 300000 ")
    List<ExportQuarterStudioDto> getQuarterStudioList(int year, int quarter);

    @Select(" select IFNULL(SUM(projectamount),0) " +
            " from zdd_company_project cp " +
            " where if(ISNULL(#{compids}) or #{compids} = '', 1=0, cp.compid in (${compids})) " +
            " and state in(10,12,13,15,20) " +
            " and YEAR(projectstart) = YEAR(#{month}) " +
            " and QUARTER(projectstart) = QUARTER(#{month}) ")
	BigDecimal getquarterTotalAmountByCompids(String compids, Date month);

    @Select(" select DISTINCT cp.compid " +
            " from zdd_company_project cp " +
            " where if(ISNULL(#{compids}) or #{compids} = '', 1=0, cp.compid in (${compids})) " +
            " and state in(10,12,13,15,20) " +
            " and YEAR(projectstart) = YEAR(#{month}) " +
            " and QUARTER(projectstart) = QUARTER(#{month}) ")
	List<Integer> getTradeCompanyByCompids(String compids, Date month);

    @Select("select DATE(cp.projectfinish) as date,count(cp.projectid) as count from zdd_company_project cp " +
            "where cp.state = 20 and DATE(projectfinish) >= '2020-01-01'" + "group by DATE(projectfinish)")
    List<CountByDate> countByDate();

    @Select(" select c.compid,c.compname,ca.business_manager_id,bm.business_manager " +
            " from zdd_company c " +
            " join zdd_company_account ca on ca.compid = c.compid " +
            " join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " where c.state = 4 and ca.realname_auth = 2 " +
            " and ISNULL((select SUM(cp.projectdealamount) " +
            " from zdd_company_project cp " +
            " where cp.compid = c.compid " +
            " and cp.state = 20 " +
            " and DATE(cp.projectfinish) = DATE(#{date})))")
	List<CompanyManagerDto> getNoneTradeCompanyManagerByMonth(Date date);

    @Select(" select c.compid,c.compname " +
            " from zdd_company c " +
            " join zdd_company_account ca on ca.compid = c.compid " +
            " where c.state = 4 and ca.realname_auth = 2 " +
            " and ISNULL((select SUM(projectdealamount) " +
            " from zdd_company_project cp " +
            " where cp.compid = c.compid " +
            " and DATE(cp.projectfinish) between DATE(#{from}) AND DATE(#{to})))")
    List<CompanyBriefDto> getThreeMonthNoneTradeCompany(Date from, Date to);

    @Select(" select ms.userid , ms.username " +
            " from zdd_maker_studio ms " +
            " where ms.state = 2 " +
            " and ISNULL((select SUM(cp.projectdealamount) " +
            " from zdd_company_project cp " +
            " where cp.userid = ms.userid " +
            " and cp.state = 20 " +
            " and DATE(cp.projectfinish) between DATE(#{from}) AND DATE(#{to})))")
    List<MakerStudio> getNoneTradeMaker(Date from, Date to);

    @Select(" select cp.*,c.compemail,c.creditcode,us.specialinvoice,us.invoicescope," +
            " ca.business_manager_id,bm.business_manager as businessManagerName,spc.projectcategory as categoryname " +
            " from zdd_company_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " join zdd_maker_studio us on us.userid = cp.userid " +
            " join zdd_company_account ca on ca.compid = cp.compid " +
            " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
            " where cp.compid = #{compid} " +
            " and cp.state in(10,12,13,15,20) " +
            " and YEAR(projectstart) = YEAR(#{date}) " +
            " and MONTH(projectstart) = MONTH(#{date}) " +
            " order by cp.projectid desc ")
    List<ProjectDto> getAllProjectByMonth(@Param("compid") int compid, @Param("date") Date date);

    @Select(" select IFNULL(SUM(projectdealamount), 0) from zdd_company_project " +
            " where userid = #{userid} " +
            " and state in(13,15,20) " +
            " and projectcheckend between #{from} and #{to} ")
    BigDecimal getTotalAmountByUserid(@Param("userid") Integer userid, @Param("from") Date from, @Param("to") Date to);

    @Select(" select IFNULL(SUM(projectdealamount), 0) from zdd_company_project " +
            " where compid in(${compids}) " +
            " and state in(13,15,20) " +
            " and DATE(projectcheckend) between DATE(#{from}) and DATE(#{to}) ")
    BigDecimal getTotalAmountByCompids(@Param("compids") String compids, @Param("from") Date from, @Param("to") Date to);

    @Select(" select cp.* " +
            " from zdd_company_project cp " +
            " where cp.compid = #{compid} " +
            " and DATE(cp.projectstart) > #{expiredate} ")
	List<CompanyProject> listProjectByCompidAndStartDate(@Param("compid") Integer compid, @Param("expiredate") Date expiredate);

    /**
     * 查询创客和该企业在   当前月 当前年 项目金额
     * @param compid 企业id
     * @param userid 创客id
     * @param type 1 当月 2 当年  其他 全部
     * @return BigDecimal
     */
    @Select(" SELECT IFNULL(SUM(projectamount),0) FROM zdd_company_project " +
            " where userid = #{userid} and compid = #{compid} " +
            " and if(#{type} = 1 ,MONTH ( CURRENT_TIMESTAMP ( ) ) = MONTH ( createtime )  AND YEAR ( CURRENT_TIMESTAMP ( ) ) = YEAR ( createtime ), 1 = 1)  " +
            " and if(#{type} = 2 ,YEAR ( CURRENT_TIMESTAMP ( ) ) = YEAR ( createtime ), 1 = 1) " +
            " AND state IN ( 10, 12, 13, 20 ) ")
    BigDecimal getProjectAmountByUserid(@Param("compid") int compid,@Param("userid") int userid,@Param("type") int type);

    /**
     * 创客和该企业 累计项目笔数
     * @param compid  企业id
     * @param userid 创客id
     * @return int
     */
    @Select(" SELECT count( * ) FROM zdd_company_project " +
            " WHERE userid = #{userid} AND compid = #{compid} AND state IN ( 10, 12, 13, 20 )")
    int getProjectByUseridAndCompidCount(@Param("compid") int compid,@Param("userid") int userid);


    /**
     * 该创客目前参与的 未完成的项目个数
     * @param userid 创客userid
     * @return int
     */
    @Select(" SELECT count( * )  FROM zdd_company_project WHERE state NOT IN ( 1, 20, 40, 44 ) AND userid = #{userid}")
    int notCompleteProjectCountByUserid(@Param("userid") int userid);


    /**
     * 查询是否已经存在该提现申请项目
     * @param partnerWithdrawId 提现申请ID
     * @return int
     */
    @Select(" SELECT count(*)  FROM zdd_company_project WHERE state != 44" +
            " and partner_withdraw_id = #{partnerWithdrawId}")
    int queryProjectByPartnerWithDrawId(int partnerWithdrawId);


    /**
     * 查询创客在   当前年 和 全部 项目金额
     * @param userid 创客id
     * @param type 1 当年  其他 全部
     * @return BigDecimal
     */
    @Select(" SELECT IFNULL(SUM(projectamount),0) FROM zdd_company_project " +
            " where userid = #{userid} " +
            " and if(#{type} = 1 ,YEAR ( CURRENT_TIMESTAMP ( ) ) = YEAR ( createtime ), 1 = 1) " +
            " AND state IN ( 10, 12, 13, 20 ) ")
    BigDecimal getProjectAmountByUseridAllCompany(@Param("userid") int userid,@Param("type") int type);


    @Select(" SELECT " +
            " DATE_FORMAT( p.createtime, '%Y' ) AS years," +
            " DATE_FORMAT( p.createtime, '%m' ) AS months, " +
            " IFNULL( sum( CASE ma.opening_location WHEN 1 THEN p.projectamount END ), 0 ) AS tianJinProjectamount, " +
            " IFNULL( sum( CASE ma.opening_location WHEN 1 THEN p.projectservicecharge END ), 0 ) AS tianJinProjectservicecharge, " +
            " IFNULL( sum( CASE ma.opening_location WHEN 1 THEN p.projecttaxfee END ), 0 ) AS tianJinProjecttaxfee, " +
            " IFNULL( " +
            " sum( CASE ma.opening_location WHEN 1 THEN p.projectservicecharge END ) + sum( CASE ma.opening_location WHEN 1 THEN p.projecttaxfee END ),0 " +
            " ) AS tianJinAllTaxFee, " +
            " IFNULL( sum( CASE ma.opening_location WHEN 4 THEN p.projectamount END ), 0 ) AS zhouShanProjectamount, " +
            " IFNULL( sum( CASE ma.opening_location WHEN 4 THEN p.projectservicecharge END ), 0 ) AS zhouShanProjectservicecharge, " +
            " IFNULL( sum( CASE ma.opening_location WHEN 4 THEN p.projecttaxfee END ), 0 ) AS zhouShanProjecttaxfee, " +
            " IFNULL( " +
            " sum( CASE ma.opening_location WHEN 4 THEN p.projectservicecharge END ) + sum( CASE ma.opening_location WHEN 4 THEN p.projecttaxfee END ), 0 " +
            " ) AS zhouShanAllTaxFee " +
            " FROM zdd_company_project p " +
            " LEFT JOIN zdd_maker_account ma ON ma.userid = p.userid  " +
            " WHERE " +
            " p.state = 20 AND " +
            " ma.opening_location IN ( 1, 4 )  " +
            " AND YEAR ( p.createtime ) = #{years}  " +
            " GROUP BY months ")
    List<MonthZhiduoTradingVolumeDto> getZhiduoTradingVolumeListGroupByMonth(int years);


    @Select(" select cp.createtime,cp.projectname,cp.username,cp.projectstart,cp.realname as name," +
            " cp.projectamount,cp.projectcycle,cp.state,spc.projectcategory as categoryname,cp.auto_check" +
            " from zdd_company_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " join zdd_maker_studio us on us.userid = cp.userid " +
            " join zdd_maker_account ma on ma.userid = cp.userid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
            " where cp.compid = #{compid} " +
            " and if(#{state}=0,cp.state NOT IN(40,44), cp.state = #{state}) " +
            " and if(#{categoryid} = 0 , 1=1, cp.categoryid = #{categoryid}) " +
            " and DATE(cp.createtime) between DATE(#{startDate}) and DATE(#{endDate}) " +
            " and concat(IFNULL(cp.projectname,''),IFNULL(cp.compname,''),IFNULL(cp.username,'')) like '%${name}%' " +
            " order by cp.state,cp.createtime desc ")
    List<ExportProjectListDto> exportProjectList(@Param("compid") int compid, @Param("name") String name, @Param("categoryid") int categoryid, @Param("state") int state,
                                                           @Param("startDate") Date startDate, @Param("endDate") Date endDate);

    /**
     * 通过企业id和创客id查询发单记录
     * @param companyId 企业id
     * @param userId 创客id
     * @return 发单列表
     */
    @Select("SELECT zcp.projectid, " +
            "zcp.projectname, " +
            "zcp.type, " +
            "zc.compid, " +
            "zcp.compname, " +
            "zc.complogo, " +
            "zcp.projectamount, " +
            "zcp.projectdealamount, " +
            "zcp.invoicetype, " +
            "projectstart, " +
            "projectfinish, " +
            "zcp.pay_id, " +
            "zcp.offline_pay_state," +
            "zcp.state,zcp.bid_type,zcp.bid_end,zcp.bid_project_finish,zcp.bid_verify_remark " +
            "FROM zdd_company_project zcp " +
            "JOIN zdd_company zc ON zcp.compid = zc.compid  " +
            "JOIN zdd_maker zu ON zcp.userid = zu.userid  " +
            "JOIN zdd_maker_account ma ON ma.userid = zu.userid  " +
            "JOIN zdd_maker_studio zus ON zus.userid = zu.userid " +
            "WHERE " +
            "zcp.compid = 147 " +
            "AND " +
            "zcp.userid = 178 ")
    List<UserProjectDto> getListByCompanyIdAndUserId(@Param("companyId") Integer companyId,@Param("userId") Integer userId);

    /**
     * 查询 企业N个月内的交易时段和交易额
     * @param companyName 企业名
     * @param n n个月
     * @return
     */
    @Select("SELECT zcp.compname as name,MIN(DAY(zcp.projectfinish)) start,MAX(DAY(zcp.projectfinish)) end,SUM(zcp.projectamount) gmv " +
            "FROM zdd_company_project zcp LEFT JOIN zdd_company zc ON zcp.compid = zc.compid " +
            "WHERE zcp.state BETWEEN 13 AND 20 " +
            "AND zcp.state <> 14 "+
            "AND DATE(zcp.projectfinish) BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL #{n}  MONTH),interval -day(curdate())+1 day) AND LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) " +
            "AND zcp.compname LIKE CONCAT(\"%\",IFNULL(#{companyName},\"\"),\"%\") " +
            "GROUP BY zcp.compid")
    List<StatisticLastThreeMonthsDto> getCompanyStatisticLastNMonths(@Param("companyName") String companyName,@Param("n")Integer n);

    @Select(" select IFNULL(SUM(projectdealamount), 0) from zdd_company_project where parent_projectid = #{parentProjectid}")
    BigDecimal sumLongTermProjectSubmittedAmount(Integer parentProjectid);

    @Select(" select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project cp " +
            " where userid = #{userid} " +
            " and cp.state in(${states}) " +
            " and createtime between DATE(#{from}) and DATE(#{to})")
    BigDecimal getTotalZddAmountByUserId(int userid, Date from, Date to, String states);

    @Select(" select pro.projectid,pro.projectname,pro.compid,pro.createtime as publishTime,pro.projectstart," +
            " IFNULL(pro.projectfinish, pro.projectcheckend) as projectfinish,pro.compname," +
            " pro.delivery_path,pro.username,round(pro.projectdealamount,2) as projectAmount " +
            " from zdd_company_project pro " +
            " where pro.state in(${states}) " +
            " and IF(ISNULL(#{projectIds}) or #{projectIds} = '', 1=1, pro.projectid in(${projectIds}))" +
            " and IF(ISNULL(#{compid}) or #{compid} = 0, 1=1, pro.compid = #{compid})")
    List<ExportProjectAcceptanceDto> getProjectAcceptance(Integer compid, String projectIds, String states);

    //查询 生成合同需要的数据
    @Select(" select pro.projectid,pro.compid,pro.userid,round(pro.projectamount,2) as serviceCharge,pro.createtime,pro.projectstart,pro.projectamount,pro.projectintro," +
            " pro.projectname,pro.categoryid,pro.commodity_detail_file_path,c.compname,ccp.company_name as partyName,stu.username,acc.temporary_login,acc.electronic_signature, " +
            " CONCAT('ZDD',DATE_FORMAT(pro.createtime,'%Y%m%d'),pro.projectid) as contractNo," +
            " IFNULL(pro.projectstart,pro.createtime) as contractDate " +
            " from zdd_company_project pro " +
            " left join zdd_company c on pro.compid = c.compid" +
            " left join zdd_maker_account acc on acc.userid = pro.userid " +
            " left join zdd_maker_studio stu on stu.userid = pro.userid  " +
            " left join zdd_crowdsource_company_party ccp on ccp.id = pro.party_id " +
            " where pro.projectid = #{projectId}")
    ProjectContractDto getProjectContractInfo(Integer projectId);

    @Delete("       delete " +
            "       from zdd_company_project " +
            "       where projectid in (${projectid}) " +
            "       and state in(${states}) ")
    int batchDeleteDraft(@Param("projectid") String projectid, String states);

    @Update("update zdd_company_project set state = #{state} where bid_end < #{date} and state in (6,7) and bid_type = 2 ")
    int updateOpenBidEnd(Date date, int state);

    //查询长期项目生成合同需要的数据
    @Select(" select pro.projectid,pro.compid,pro.userid,round(pro.projectdealamount,2) as serviceCharge,pro.createtime,pro.projectstart,pro.projectdealamount,pro.projectintro," +
            " pro.projectname,pro.categoryid,pro.commodity_detail_file_path,c.compname,IFNULL(ccp.company_name,'') as partyName,stu.username,acc.temporary_login,acc.electronic_signature, " +
            " CONCAT('ZDD',DATE_FORMAT(pro.createtime,'%Y%m%d'),pro.projectid) as contractNo," +
            " IFNULL(pro.projectstart,pro.createtime) as contractDate " +
            " from zdd_company_project pro " +
            " left join zdd_company c on pro.compid = c.compid" +
            " left join zdd_maker_account acc on acc.userid = pro.userid " +
            " left join zdd_maker_studio stu on stu.userid = pro.userid  " +
            " left join zdd_crowdsource_company_party ccp on ccp.id = pro.party_id " +
            " where pro.parent_projectid = #{parentProjectId}" +
            " and pro.state = 20")
    List<ProjectContractDto> getProjectContractInfoList(Integer parentProjectId);

    @Select(" select SUM(projectamount)" +
            " from zdd_company_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " left join zdd_maker_studio us on us.userid = cp.userid " +
            " left join zdd_maker_account ma on ma.userid = cp.userid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
            " where cp.compid = #{compid} " +
            " and cp.type = 1 " +
            " and if(#{state}=0, cp.state NOT IN(40,44) , cp.state = #{state}) " +
            " and if(#{categoryid} = 0 , 1=1, cp.categoryid = #{categoryid}) " +
            " and DATE(cp.createtime) between DATE(#{startDate}) and DATE(#{endDate}) " +
            " and concat(IFNULL(cp.projectname,''),IFNULL(cp.compname,''),IFNULL(cp.username,''),IFNULL(cp.realname,'')) like '%${name}%' ")
    String regularItemCountAmount(@Param("compid") int compid, @Param("name") String name, @Param("categoryid") int categoryid, @Param("state") int state,
                                  @Param("startDate") Date startDate, @Param("endDate") Date endDate);
    @Select("SELECT " +
            " c.compid,c.compname,c.openid,COUNT(cp.projectid) AS quantity,bm.business_manager " +
            "FROM " +
            " zdd_company_project cp " +
            " JOIN zdd_company c ON c.compid = cp.compid " +
            " JOIN zdd_company_account ca ON ca.compid = c.compid " +
            " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " WHERE cp.state = 12 " +
            " GROUP BY cp.compid ")
    List<ProjectCompanyDto> projectAcceptance();

    @Select(" select cp.projectid " +
            " from zdd_company_project cp " +
            " where cp.compid = #{compid} " +
            " and cp.state = 20  " +
            " and if(#{type} = 0, 1=1 , type = #{type})  " +
            " and cp.delivery_path is null" +
            " and cp.projectfinish <= DATE_SUB(CURRENT_DATE(),INTERVAL 5 DAY) ")
    List<String> getCompProjectDeliveryNum(@Param("compid") Integer compid,@Param("type") Integer type);

    @Select(" select count(*) from zdd_company_project cp " +
            " where cp.compid=#{compid} " +
            " and cp.state = 12 ")
    int getProCheckingNum(@Param("compid") int compid);

    @Select(" select sum(num) from " +
            "( " +
            " select count(*) as num from zdd_company_project cp where cp.compid = #{compid} and cp.state = 20 " +
            " union all " +
            " select count(*) as num from zdd_crowdsource_project cp where cp.compid = #{compid} and cp.state = 4 " +
            ")tmp")
    int getAllProNum(@Param("compid") int compid);


    @Select(" select count(*) " +
            " from zdd_company_project cp " +
            " where cp.compid = #{compid} " +
            " and cp.state = 20  " +
            " ${str} ")
    int getZddProjectNum(@Param("compid") int compid,
                         @Param("str") String str);

    @Select("select IFNULL(SUM(projectdealamount),0) " +
            " from zdd_company_project " +
            " where userid IN (${userIds}) " +
            " and state in(${states}) " +
            " and projectid != #{projectid} " +
            " and IF(#{timeType} = 1 ,YEAR(createtime) = YEAR(NOW()), " +
            "     createtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH))")
    BigDecimal getZddAmountByUserIds(String userIds, int projectid, String states, int timeType);

    @Select("SELECT pro.* FROM (" +
            " SELECT cp.projectid,cp.compid,cp.compname,cp.userid,2 as user_type,ma.idno,cp.realname,cp.username," +
            " cp.state,cp.projectdealamount as projectamount,cp.createtime as publishTime,1 as projectType " +
            " FROM zdd_company_project cp  " +
            " LEFT JOIN zdd_maker_account ma on cp.userid = ma.userid " +
            " WHERE state in (10,12,13,15,20)   " +
            "UNION ALL " +
            " SELECT cspi.id as projectid,csp.compid,csp.compname,cspi.userid,cspi.user_type,IF(cspi.user_type = 1,ca.idno,ma.idno) as idno, " +
            " cspi.realname,cspi.username,csp.state,cspi.project_amount,csp.createtime as publishTime,IF(work_type=1,2,3) as projectType " +
            " FROM zdd_crowdsource_project_item cspi   " +
            " JOIN zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99)  " +
            " JOIN zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (1,2,3)  " +
            " LEFT JOIN zdd_maker_account ma on cspi.userid = ma.userid and cspi.user_type = 2 " +
            " LEFT JOIN zdd_crowdsource_account ca on ca.crowdsourceid = cspi.userid and cspi.user_type = 1 " +
            " WHERE cspi.deleted = 0 " +
            "UNION ALL " +
            " SELECT epi.id as projectid,epi.compid,epi.compname,epi.entrustee_id as userid,2 AS user_type,ma.idno as idno, " +
            " epi.entrustee_name as realname,epi.entrustee_studio as username,epi.state, " +
            " IF(project_type = 6,epi.project_amount,epi.tax_inclusive_amount) as project_amount," +
            " epi.createtime as publishTime,IF(project_type in (5,6),4,5) AS projectType " +
            " FROM zdd_entrustment_project_item epi   " +
            " LEFT JOIN zdd_maker_account ma on ma.userid = epi.entrustee_id " +
            " WHERE epi.state in (2,3,4) " +
            "UNION ALL " +
            " SELECT ti.invoiceid as projectid,ti.companyid as compid,ti.company_name as compname,ti.userid,2 AS user_type,ma.idno as idno, " +
            " ma.realname,ti.username,ti.state,ti.amount as project_amount," +
            " tp.confirmtime as publishTime,6 AS projectType " +
            " FROM zdd_taxer_invoice ti " +
            " JOIN zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
            " LEFT JOIN zdd_maker_account ma on ma.userid = ti.userid " +
            " WHERE ti.deleted = 0 " +
            ")pro  " +
            "WHERE IF(ISNULL(#{projectTypes}) OR #{projectTypes} = '', 1=1, pro.projectType in (0${projectTypes}) ) " +
            "AND pro.realname LIKE CONCAT('%',IFNULL(#{realname},''),'%') " +
            "AND pro.username LIKE CONCAT('%',IFNULL(#{username},''),'%') " +
            "AND pro.idno LIKE CONCAT('%',IFNULL(#{idno},''),'%') " +
            "AND IF(ISNULL(#{publishStart}),1=1, pro.publishTime >= #{publishStart}) " +
            "AND IF(ISNULL(#{publishEnd}),1=1,pro.publishTime <= #{publishEnd}) " +
            "ORDER BY pro.publishTime DESC ")
    List<MakerAllProjectDto> getMakerAllProject(String realname,String username,String idno,String projectTypes,Date publishStart,Date publishEnd);
}
